#!/bin/bash



if [ -n "$1" ] ; then

  data_date=$1

else

  data_date=`date -d "-1 days" +%F`

fi



sql="

insert overwrite table jtp_app_ads.ads_traffic_page_view_report

select '$data_date',

       1,

       province,

       city,

       sum(session_pv),

       count(distinct device_id),

       count(distinct session_id),

       count(distinct session_id) / count(distinct device_id),

       sum(session_during_time),

       sum(session_during_time) / count(distinct session_id),

       sum(if(session_pv = 1,1,0)),

       sum(if(session_pv = 1,1,0)) / count(distinct session_id)

from jtp_app_dws.dws_event_log_session_agg

where dt = '$data_date'

group by province,city

union

select '$data_date',

       7,

       province,

       city,

       sum(session_pv),

       count(distinct device_id),

       count(distinct session_id),

       count(distinct session_id) / count(distinct device_id),

       sum(session_during_time),

       sum(session_during_time) / count(distinct session_id),

       sum(if(session_pv = 1,1,0)),

       sum(if(session_pv = 1,1,0)) / count(distinct session_id)

from jtp_app_dws.dws_event_log_session_agg

where dt > date_sub('$data_date',7)

group by province,city

union

select '$data_date',

       30,

       province,

       city,

       sum(session_pv),

       count(distinct device_id),

       count(distinct session_id),

       count(distinct session_id) / count(distinct device_id),

       sum(session_during_time),

       sum(session_during_time) / count(distinct session_id),

       sum(if(session_pv = 1,1,0)),

       sum(if(session_pv = 1,1,0)) / count(distinct session_id)

from jtp_app_dws.dws_event_log_session_agg

where dt > date_sub('$data_date',30)

group by province,city;

"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${sql}"